﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using Trying1;

namespace Trying1.Classes
{
    class CheckoutDBManager
    {
        public static int UpdateGuestCheckOutDate(int roomNo, DateTime checkOutDate)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Booking SET checkOutDate=@checkOutdate where roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNo);
                comm.Parameters.AddWithValue("@checkOutDate", checkOutDate);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        public static int UpdateGuestTotalPrice(int roomNo, decimal totalPrice)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Booking SET totalPrice=@totalPrice where roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNo);
                comm.Parameters.AddWithValue("@totalPrice", totalPrice);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        public static int InsertInvoice(Invoice m)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(bookingId,roomNum,noOfDayStayed,noOfNightStayed,paymentMode,checkInDate,checkOutDate,roomRate,roomType,additionalCharges,totalPrice)" +
                    " VALUES (@bookingId,@roomNum,@noOfDayStayed,@noOfNightStayed,@paymentMode,@checkInDate,@checkOutDate,@roomRate,@roomType,@additionalCharges,@totalPrice)";
                
                comm.Parameters.AddWithValue("@bookingId", m.bookingID);
                comm.Parameters.AddWithValue("@roomNum", m.roomNum);
                comm.Parameters.AddWithValue("@noOfDayStayed", m.noOfDayStayed);
                comm.Parameters.AddWithValue("@noOfNightStayed", m.noOfNightStayed);
                comm.Parameters.AddWithValue("@paymentMode", m.paymentMode);
                comm.Parameters.AddWithValue("@checkInDate", m.checkInDate);
                comm.Parameters.AddWithValue("@checkOutDate", m.checkOutDate);
                comm.Parameters.AddWithValue("@roomRate", m.roomRate);
                comm.Parameters.AddWithValue("@roomType", m.roomType);
                comm.Parameters.AddWithValue("@additionalCharges", m.addCost);
                comm.Parameters.AddWithValue("@totalPrice", m.totalPrice);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static Booking GetBookingByroomNum(int roomNum)
        {
            Booking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Booking WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Booking();
                    m.bookingId = (int)dr["bookingId"];
                    m.roomNum = (int)dr["roomNum"];
                    m.guestNo = (int)dr["guestNo"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static Guest GetCreditCardInfoByGuestNo(int guestNo)
        {
           Guest m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Guest WHERE guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Guest();
                    m.guestNo = (int)dr["guestNo"];
                    m.firstName = (string)dr["firstName"];
                    m.lastName = (string)dr["lastName"];
                    m.noOfGuest = (int)dr["noOfGuest"];
                    m.noOfChildren = (int)dr["noOfChildren"];
                    m.phoneNum = (Int64)dr["phoneNum"];
                    m.emailAddress = (string)dr["emailAddress"];
                    m.streetAddress = (string)dr["streetAddress"];
                    m.houseNo = (string)dr["houseNo"];
                    m.postalCode = (string)dr["postalCode"];
                    m.country = (string)dr["country"];
                    m.paymentType = (string)dr["paymentType"];
                    m.roomNum = (int)dr["roomNum"];
                    m.ccNo = (string)dr["ccNo"];
                    m.ccName = (string)dr["ccName"];
                    m.expDate = (DateTime)dr["expDate"];

                    
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static Invoice GetInvoiceIDByBookingId(int bookingId)
        {
           Invoice m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT invoiceId FROM Invoice WHERE bookingId=@bookingId";
                comm.Parameters.AddWithValue("@bookingID", bookingId);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Invoice();
                    m.invoiceID = (int)dr["invoiceId"];
                    
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static ArrayList GetAllBookingByroomNum(int roomNum)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Booking WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Booking m = new Booking();
                    m.bookingId = (int)dr["bookingId"];
                    m.roomNum = (int)dr["roomNum"];
                    m.guestNo = (int)dr["guestNo"];
                    m.checkInDate = (DateTime)dr["checkInDate"];
                    m.checkOutDate = (DateTime)dr["checkOutDate"];
                    m.addReq = (string)dr["addReq"];
                    m.roomRates = (decimal)dr["roomRates"];
                    result.Add(m);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static Booking GetCheckInByGuestNo(int guestNo)
        {
            Booking m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDB"].ConnectionString;
                conn.Open(); SqlCommand comm = new SqlCommand();
                comm.Connection = conn; comm.CommandText = "SELECT * FROM Booking WHERE guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new Booking();
                    m.checkInDate = (DateTime)dr["checkInDate"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }

    }
}
